CREATE EXTERNAL TABLE jms_dwd.dwd_appeal_info_new_base_hi
(
    id                              bigint COMMENT 'ID',
    appeal_no                       string COMMENT '申诉编号',
    waybill_no                      string COMMENT '运单号',
    user_name                       string COMMENT '申诉人姓名',
    phone                           string COMMENT '申诉人手机号码',
    email                           string COMMENT 'email',
    status                          tinyint COMMENT '处理状态 1:未受理 2:已受理 3:已答复 4:已拒绝 5:已结案 6:待分配 7:待审核',
    type                            tinyint COMMENT '申诉种类: 1快递服务、2函件、3包件、4汇兑、5报刊、6集邮、7其他',
    cause                           tinyint COMMENT '申诉原因: 1.快件延误；2.快件丢失短少；3.快件损毁；4.收寄服务；5.投递服务；6.售后服务；7.违规收费8.其他9.代收货款',
    appeal_time                     timestamp COMMENT '申诉时间',
    province_id                     bigint COMMENT '申诉省id',
    province_desc                   string COMMENT '申诉省',
    city_id                         bigint COMMENT '申诉市id',
    city_desc                       string COMMENT '申诉市',
    description                     string COMMENT '申诉说明',
    trace                           string COMMENT '流转信息jsonArray',
    evidence                        string COMMENT '申诉证据信息jsonArray',
    is_insured                      tinyint COMMENT '是否保价1：是 2：否',
    insured_value                   double COMMENT '保价金额',
    acceptance_office_province_id   bigint COMMENT '受理局省id(转办)',
    acceptance_office_province_desc string COMMENT '受理局省',
    acceptance_office_city_id       bigint COMMENT '受理局市id(转办)',
    acceptance_office_city_desc     string COMMENT '受理局市',
    acceptance_office_time          timestamp COMMENT '受理局受理时间',
    valid                           tinyint COMMENT '是否有效（1：有效 2：无效）',
    economic_loss                   double COMMENT '挽回经济损失',
    discontact_count                smallint COMMENT '未联系到用户次数',
    complaints_result               tinyint COMMENT '投诉结果1：投诉无果2：对投诉结果不满意',
    remark                          string COMMENT '备注信息',
    complain_info                   string COMMENT '投诉结果补充',
    express_time                    timestamp COMMENT '邮寄时间',
    close_time                      timestamp COMMENT '结案时间',
    close_office_province_id        bigint COMMENT '结案省id',
    close_office_province_desc      string COMMENT '结案省',
    close_office_city_id            bigint COMMENT '结案市id',
    close_office_city_desc          string COMMENT '结案市',
    blame_province_id               bigint COMMENT '责任省id',
    blame_province_desc             string COMMENT '责任省',
    blame_city_id                   bigint COMMENT '责任市id',
    blame_city_desc                 string COMMENT '责任市',
    sender_name                     string COMMENT '寄件人姓名',
    sender_phone                    string COMMENT '寄件人手机号',
    sender_address                  string COMMENT '寄件人地址',
    send_network_id                 bigint COMMENT '发件网点id',
    send_network_code               string COMMENT '发件网点code',
    send_network_name               string COMMENT '发件网点name',
    receiver_name                   string COMMENT '收件人姓名',
    receiver_phone                  string COMMENT '收件人手机号',
    receiver_address                string COMMENT '收件人地址',
    dispatch_network_id             bigint COMMENT '派件网点id',
    dispatch_network_code           string COMMENT '派件网点code',
    dispatch_network_name           string COMMENT '派件网点name',
    trans_count                     smallint COMMENT '转办次数',
    trans_reason                    tinyint COMMENT '转办企业类型 1 正常转办 2企业答复不规范转办',
    trans_time                      timestamp COMMENT '转办时间',
    accept_network_id               bigint COMMENT '受理网点id',
    accept_network_code             string COMMENT '受理网点code',
    accept_network_name             string COMMENT '受理网点name',
    accept_time                     timestamp COMMENT '受理网点-受理时间',
    assign_time                     timestamp COMMENT '最后分配时间',
    assign_type                     tinyint COMMENT '分配类型 1:分配网点 2分配客服',
    accept_user_id                  bigint COMMENT '受理人id',
    accept_user_code                string COMMENT '受理人编码',
    accept_user_name                string COMMENT '受理人名称',
    blame_network_id                bigint COMMENT '责任网点id',
    blame_network_code              string COMMENT '责任网点code',
    blame_network_name              string COMMENT '责任网点name',
    blame_proxy_id                  bigint COMMENT '责任代理区网点id',
    blame_proxy_code                string COMMENT '责任代理区网点code',
    blame_proxy_name                string COMMENT '责任代理区网点name',
    is_upgrade                      tinyint COMMENT '是否升级（1：是 2：否）',
    upgrade_network_id              bigint COMMENT '升级处理网点id',
    upgrade_network_code            string COMMENT '升级处理网点code',
    upgrade_network_name            string COMMENT '升级处理网点name',
    upgrade_time                    timestamp COMMENT '升级时间',
    process_user_id                 bigint COMMENT '最后处理人id',
    process_user_code               string COMMENT '最后处理人code',
    process_user_name               string COMMENT '最后处理人name',
    process_time                    timestamp COMMENT '最后处理时间',
    create_time                     timestamp COMMENT '创建时间',
    update_time                     timestamp COMMENT '更新时间',
    audit_opinion                   string COMMENT '审核意见',
    audit_user_id                   bigint COMMENT '审核人ID',
    audit_user_code                 string COMMENT '审核人编码',
    audit_user_name                 string COMMENT '审核人姓名',
    accept_network_type             tinyint COMMENT '网点标识,1网点,2转运中心,3集散点 4:其他',
    accept_proxy_id                 bigint COMMENT '受理网点所属代理区ID',
    accept_proxy_code               string COMMENT '受理网点所属代理区编码',
    accept_proxy_name               string COMMENT '受理网点所属代理区名称',
    accept_franchisee_id            bigint COMMENT '受理网点所属加盟商ID',
    accept_franchisee_code          string COMMENT '受理网点所属加盟商编码',
    accept_franchisee_name          string COMMENT '受理网点所属加盟商名称',
    monitoring_time                 timestamp COMMENT '监控报表时间',
    process_duration                bigint COMMENT '处理时长',
    response_duration               bigint COMMENT '响应时长',
    is_monitor_history              tinyint COMMENT '是否是监控报表的历史数据 1是 2否',
    is_repeat                       tinyint COMMENT '是否重复(1:不重复,2:重复)',
    delivery_code                   string COMMENT '派件员编码',
    delivery_name                   string COMMENT '派件员名称',
    accept_network_mr_code          string COMMENT '受理网点管理大区编号',
    accept_network_mr_name          string COMMENT '受理网点管理大区名称',
    order_source_code               string COMMENT '工单来源编码',
    order_source_name               string COMMENT '工单来源名称',
    transfer_order_time             timestamp COMMENT '转单时间',
    channel                         tinyint COMMENT '渠道：1；百世，2；极兔',
    is_dispatcher                   tinyint COMMENT '是否通知发派送员 1:否 2:是'
)
    COMMENT '小时申诉单-清洗表'
    PARTITIONED BY (dt string COMMENT '时间分区(yyyy-MM-dd)')
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
        WITH SERDEPROPERTIES (
        'serialization.format' = '1'
        )
    STORED AS
        INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION '/dw/hive/jms_dwd.db/external/dwd_appeal_info_new_base_hi'
;